/*
 * Copyright (C) 2013 SeekDeepWithin.com
 *
 * This program is free software; you can redistribute it and/or
 * modify it under the terms of the GNU General Public License
 * as published by the Free Software Foundation; either version 2
 * of the License, or (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program; if not, write to the Free Software
 * Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA  02111-1307, USA.
 */
package com.seekdeepwithin.DataAccess;

import com.almworks.sqlite4java.*;
import com.seekdeepwithin.*;
import com.seekdeepwithin.Data.*;
import com.seekdeepwithin.Data.Book;
import java.io.*;
import java.util.*;
import javafx.application.*;
import javafx.beans.property.*;
import javafx.concurrent.*;
import javafx.scene.control.*;

/**
 * User: Jonathan Montiverdi Date: Sep 23, 2013
 *
 * Service used to import a book from the website.
 */
public class ImportBookService extends Service<Void>
{
   private ObjectProperty<Book> book = new SimpleObjectProperty ();

   /**
    * Initializes a new import book service.
    * @param book Book to import.
    */
   public ImportBookService (Book book)
   {
      this.setBook (book);
   }

   /**
    * Sets the book to import.
    *
    * @param book The book to import.
    */
   public final void setBook (Book book)
   {
      this.book.set (book);
   }

   /**
    * Gets the book to import.
    *
    * @return The book to import.
    */
   public final Book getBook ()
   {
      return this.book.get ();
   }

   /**
    * Gets the book to import property.
    *
    * @return Book to import property.
    */
   public final ObjectProperty<Book> bookProperty ()
   {
      return this.book;
   }

   @Override
   protected Task<Void> createTask () {
      final Book _Book = getBook ();
      final SdwApi _Api = SdwApi.getInstance ();
      final SQLiteConnection _Db = new SQLiteConnection (new File (SdwDatabase.DB_PATH));
      return new Task<Void> () {
         @Override
         protected Void call () throws Exception {
            updateMessage ("Importing Book...");
            updateProgress (1, 10);
            try
            {
               _Db.open (false);
               _Db.exec ("BEGIN TRANSACTION;");
               addBook (_Book);
               List<Style> styles = _Api.getAllStyles ();
               SQLiteStatement statement = _Db.prepare ("REPLACE INTO styles (style_id, style_type_id, style) VALUES (?,?,?);");
               for (Style style : styles) {
                  statement.bind (1, style.getId ());
                  statement.bind (2, style.getTypeId ());
                  statement.bind (3, style.getStyle ());
                  statement.step ();
                  statement.reset ();
               }
               statement.dispose ();
               updateProgress (2.5, 10);

               addSubBooks ();
               updateProgress (4, 10);

               List<SubBook> subBooks = _Book.getSubBooks ();
               int subBookCount = subBooks.size ();
               double increment = 2 / subBookCount;
               double progress = 4;
               for (SubBook subBook : subBooks) {
                  addChapters (subBook);
                  progress += increment;
                  updateProgress (progress, 10);
               }
               updateProgress (6, 10);

               increment = 4 / subBookCount;
               progress = 6;
               for (SubBook subBook : subBooks) {
                  List<Chapter> chapters = subBook.getChapters ();
                  for (Chapter chapter : chapters) {
                     addPassages (chapter);
                  }
                  progress += increment;
                  updateProgress (progress, 10);
               }
               _Db.exec ("COMMIT;");
               _Db.dispose ();
            }
            catch (SQLiteException exception)
            {
               Dialogs.showErrorDialog (MainController.getInstance ().getStage (),
                                        "An error occured while importing the selected book.",
                                        "An error was detected with the database.", "Error", exception);
            }
            updateProgress (10, 10);
            updateMessage ("Completed!");
            Platform.runLater (new Runnable () {
               @Override public void run () {
                  MainController.getInstance ().bookListUpdated ();
               }
            });
            return null;
         }

         /**
          * Adds the given book to the local database.
          * @param book Book to add to database.
          */
         private void addBook (Book book) throws SQLiteException {
            SQLiteStatement statement = _Db.prepare ("REPLACE INTO books "
                                                      + "(book_id, is_subbook, title, subtitle, abbreviation, author, date, amazonid, "
                                                      + "has_chapters, has_subbooks, info) VALUES (?,?,?,?,?,?,?,?,?,?,?)");
            statement.bind (1, book.getId ());
            statement.bind (2, book.getIsSubBook () ? 1 : 0);
            statement.bind (3, book.getTitle ());
            statement.bind (4, book.getSubTitle ());
            statement.bind (5, book.getAbbreviation ());
            statement.bind (6, book.getAuthor ());
            statement.bind (7, book.getDate ());
            statement.bind (8, book.getAmazonId ());
            statement.bind (9, book.HasChapters () ? 1 : 0);
            statement.bind (10, book.HasSubBooks () ? 1 : 0);
            statement.bind (11, book.getInfo ());
            statement.step ();
            statement.dispose ();
            addSources (book);
            addTranslators (book);
         }

         /**
          * Adds the sources for the given book.
          * @param book The book to add sources for.
          */
         private void addSources (Book book) throws SQLiteException {
            if (book.getSources ().size () > 0) {
               SQLiteStatement bookSourceStatement = _Db.prepare ("REPLACE INTO book_sources "
                       + "(book_id, source_id) VALUES (?,?);");
               SQLiteStatement sourceStatement = _Db.prepare ("REPLACE INTO sources "
                       + "(source_id, source_name_id, source) VALUES (?,?,?);");
               SQLiteStatement sourceNameStatement = _Db.prepare ("REPLACE INTO source_names "
                       + "(source_name_id, source_name) VALUES (?,?);");
               for (Source source : book.getSources ()) {
                  /*SQLiteStatement statement = _Db.prepare ("SELECT * FROM book_sources WHERE book_id=? AND source_id=?");
                  statement.bind (1, book.getId ());
                  statement.bind (2, source.getId ());
                  if (!statement.step ()) {*/
                     sourceStatement.bind (1, source.getId ());
                     sourceStatement.bind (2, source.getSourceNameId ());
                     sourceStatement.bind (3, source.getSource ());
                     sourceStatement.step ();
                     sourceStatement.reset ();
                     sourceNameStatement.bind (1, source.getSourceNameId ());
                     sourceNameStatement.bind (2, source.getName ());
                     sourceNameStatement.step ();
                     sourceNameStatement.reset ();
                     bookSourceStatement.bind (1, book.getId ());
                     bookSourceStatement.bind (2, source.getId ());
                     bookSourceStatement.step ();
                     bookSourceStatement.reset ();
                  //}
               }
               bookSourceStatement.dispose ();
               sourceStatement.dispose ();
               sourceNameStatement.dispose ();
            }
         }

         /**
          * Adds the translators for the given book.
          * @param book The book to add translators for.
          */
         private void addTranslators (Book book) throws SQLiteException {
            if (book.getTranslators ().size () > 0) {
               SQLiteStatement bookStatement = _Db.prepare ("REPLACE INTO book_translators "
                       + "(book_id, translator_id) VALUES (?,?);");
               SQLiteStatement sourceStatement = _Db.prepare ("REPLACE INTO translators "
                       + "(translator_id, translator) VALUES (?,?);");
               for (BaseDbObject translator : book.getTranslators ()) {
                  /*SQLiteStatement statement = _Db.prepare ("SELECT * FROM book_translators WHERE book_id=? AND translator_id=?");
                  statement.bind (1, book.getId ());
                  statement.bind (2, translator.getId ());
                  if (!statement.step ()) {*/
                     sourceStatement.bind (1, translator.getId ());
                     sourceStatement.bind (2, translator.getName ());
                     sourceStatement.step ();
                     sourceStatement.reset ();
                     bookStatement.bind (1, book.getId ());
                     bookStatement.bind (2, translator.getId ());
                     bookStatement.step ();
                     bookStatement.reset ();
                  //}
               }
               bookStatement.dispose ();
               sourceStatement.dispose ();
            }
         }

         /**
          * Adds all sub books for the given book.
          */
         private void addSubBooks () throws SQLiteException {
            updateMessage ("Importing Sub books...");
            _Api.getSubBooks (_Book);
            updateProgress (3, 10);
            List<SubBook> subBooks = _Book.getSubBooks ();
            for (SubBook subBook : subBooks) {
               this.addBook (subBook);

               SQLiteStatement subBookListStatement = _Db.prepare ("REPLACE INTO subbook_list (sub_book_list_id, book_id, sub_book_id, order_index) VALUES (?,?,?,?);");
               subBookListStatement.bind (1, subBook.getSubBookListId ());
               subBookListStatement.bind (2, _Book.getId ());
               subBookListStatement.bind (3, subBook.getId ());
               subBookListStatement.bind (4, subBook.getOrder ());
               subBookListStatement.step ();
               subBookListStatement.dispose ();

               if (subBook.getLinkedId () != -1) {
                  SQLiteStatement linkStatement = _Db.prepare ("REPLACE INTO subbook_list_links (sub_book_list_id, sub_book_list_id_link) VALUES (?,?);");
                  linkStatement.bind (1, subBook.getId ());
                  linkStatement.bind (2, subBook.getLinkedId ());
                  linkStatement.step ();
                  linkStatement.dispose ();
               }
            }
         }

         /**
          * Downloads the chapters for the given sub book and adds them to the local database.
          * @param subBook Sub book to get chapters for.
          */
         private void addChapters (SubBook subBook) throws SQLiteException {
            updateMessage ("Importing Chapters...");
            _Api.getChapters (subBook);
            List<Chapter> chapters = subBook.getChapters ();
            for (Chapter chapter : chapters) {
               SQLiteStatement statement =_Db.prepare ("REPLACE INTO chapters (chapter_id, name) VALUES (?,?);");
               statement.bind (1, chapter.getId ());
               statement.bind (2, chapter.getName ());
               statement.step ();
               statement.dispose ();

               statement = _Db.prepare ("REPLACE INTO chapter_list (chapter_list_id, sub_book_list_id, chapter_id, order_index) VALUES (?,?,?,?);");
               statement.bind (1, chapter.getChapterListId ());
               statement.bind (2, subBook.getSubBookListId ());
               statement.bind (3, chapter.getId ());
               statement.bind (4, chapter.getOrder ());
               statement.step ();
               statement.dispose ();

               BaseDbObject footer = chapter.getFooter ();
               if (footer != null) {
                  statement =_Db.prepare ("REPLACE INTO chapter_footers (chapter_list_id, footer_id) VALUES (?,?);");
                  statement.bind (1, chapter.getChapterListId ());
                  statement.bind (2, footer.getId ());
                  statement.step ();
                  statement.dispose ();

                  statement =_Db.prepare ("REPLACE INTO footers (footer_id, footer) VALUES (?,?);");
                  statement.bind (1, footer.getId ());
                  statement.bind (2, footer.getName ());
                  statement.step ();
                  statement.dispose ();
               }

               BaseDbObject header = chapter.getHeader ();
               if (header != null) {
                  statement =_Db.prepare ("REPLACE INTO chapter_headers (chapter_list_id, header_id) VALUES (?,?);");
                  statement.bind (1, chapter.getChapterListId ());
                  statement.bind (2, header.getId ());
                  statement.step ();
                  statement.dispose ();

                  statement =_Db.prepare ("REPLACE INTO headers (header_id, header) VALUES (?,?);");
                  statement.bind (1, header.getId ());
                  statement.bind (2, header.getName ());
                  statement.step ();
                  statement.dispose ();
               }
            }
         }

         /**
          * Downloads and adds the passages for the given chapter.
          * @param chapter Chapter to get passages for.
          */
         private void addPassages (Chapter chapter) throws SQLiteException {
            updateMessage ("Importing Passages...");
            _Api.getPassages (chapter);
            List<Passage> passages = chapter.getPassages ();
            for (Passage passage : passages) {
               SQLiteStatement statement = _Db.prepare ("REPLACE INTO passages (passage_id, passage) VALUES (?,?);");
               statement.bind (1, passage.getId ());
               statement.bind (2, passage.getText ());
               statement.step ();
               statement.dispose ();

               statement = _Db.prepare ("REPLACE INTO passage_list (chapter_list_id, passage_id, passage_number, order_index) VALUES (?,?,?,?);");
               statement.bind (1, chapter.getChapterListId ());
               statement.bind (2, passage.getId ());
               statement.bind (3, passage.getNumber ());
               statement.bind (4, passage.getOrder ());
               statement.step ();
               statement.dispose ();

               if (passage.getHeader () != null) {
                  statement = _Db.prepare ("REPLACE INTO passage_headers (passage_id, header_id) VALUES (?,?);");
                  statement.bind (1, passage.getId ());
                  statement.bind (2, passage.getHeader ().getId ());
                  statement.step ();
                  statement.dispose ();

                  statement = _Db.prepare ("REPLACE INTO headers (header_id, header) VALUES (?,?);");
                  statement.bind (1, passage.getHeader ().getId ());
                  statement.bind (2, passage.getHeader ().getName ());
                  statement.step ();
                  statement.dispose ();
               }

               Map<Integer, BaseDbObject> footers = passage.getFooters ();
               if (footers != null) {
                  for (Map.Entry<Integer, BaseDbObject> footer : footers.entrySet ()) {
                     Integer footerIndex = footer.getKey ();
                     BaseDbObject footerData = footer.getValue ();

                     statement = _Db.prepare ("REPLACE INTO passage_footers (passage_id, footer_id, footer_index) VALUES (?,?,?);");
                     statement.bind (1, passage.getId ());
                     statement.bind (2, footerData.getId ());
                     statement.bind (3, footerIndex);
                     statement.step ();
                     statement.dispose ();

                     statement = _Db.prepare ("REPLACE INTO footers (footer_id, footer) VALUES (?,?);");
                     statement.bind (1, footerData.getId ());
                     statement.bind (2, footerData.getName ());
                     statement.step ();
                     statement.dispose ();
                  }
               }

               List<Link> links = passage.getLinks ();
               if (links != null) {
                  for (Link link : links) {
                     statement = _Db.prepare ("REPLACE INTO passage_links (passage_id, start_index, end_index, link_id) VALUES (?,?,?,?);");
                     statement.bind (1, passage.getId ());
                     statement.bind (2, link.getStartIndex ());
                     statement.bind (3, link.getEndIndex ());
                     statement.bind (4, link.getId ());
                     statement.step ();
                     statement.dispose ();

                     statement = _Db.prepare ("REPLACE INTO links (link_id, area_id, link) VALUES (?,?,?);");
                     statement.bind (1, link.getId ());
                     statement.bind (2, link.getAreaId ());
                     statement.bind (3, link.getLink ());
                     statement.step ();
                     statement.dispose ();
                  }
               }

               List<Link> defs = passage.getDefinitions ();
               if (defs != null) {
                  for (Link def : defs) {
                     statement = _Db.prepare ("REPLACE INTO passage_definitions (passage_id, start_index, end_index, glossary_item_id) VALUES (?,?,?,?);");
                     statement.bind (1, passage.getId ());
                     statement.bind (2, def.getStartIndex ());
                     statement.bind (3, def.getEndIndex ());
                     statement.bind (4, def.getId ());
                     statement.step ();
                     statement.dispose ();
                  }
               }

               List<Style> styles = passage.getStyles ();
               if (styles != null) {
                  for (Style style : styles) {
                     statement = _Db.prepare ("REPLACE INTO passage_styles (passage_style_id, passage_id, start_index, end_index, style_id) VALUES (?,?,?,?,?);");
                     statement.bind (1, style.getPassageStyleId ());
                     statement.bind (2, passage.getId ());
                     statement.bind (3, style.getStartIndex ());
                     statement.bind (4, style.getEndIndex ());
                     statement.bind (5, style.getId ());
                     statement.step ();
                     statement.dispose ();
                  }
               }
            }
         }
      };
   }
}
